set echo off
--------------------------------------------------------------------------------
-- @name: prof_latch
-- @author: dion cho
-- @note: profile latch
-- @usage: @prof_latch 100000 151 %
--------------------------------------------------------------------------------

define __SAMPLE = &1
define __SID = "&2"
define __LATCHNAME = "&3"

select /*+ ordered use_nl(t1 l) */
  l.sid,
  l.laddr,
  l.name,
  count(*) as hits
from
  (select /*+ no_merge */ 1 from dual connect by level <= &__SAMPLE) t1,
  (select /*+ no_merge */ * from v$latchholder
      where sid in (&__SID) and name like '&__LATCHNAME') l
group by
  l.sid, l.laddr, l.name
order by
  4 desc
;

set echo on
